(Fwd) RE: Accessing a dBASE 5 (.dbf) file as a data source in

From: Eric D. Pierce <PierceED_at_csus.edu>
Date: Mon, 11 Dec 2000 10:30:05 -0800
Message-Id: <10707.124216@fatcity.com>

• Forwarded message follows ------- Date sent: Sun, 27 Aug 2000 23:35:43 -0800 To: Multiple recipients of list O D T U G - D E V 2 K - L < O D T U G - D E V 2 K - L @ f a t c i t y . c o m > From: Mashkoor Ali Khan <Mashkoor.AliKhan_at_almarai.com> Subject: RE: Accessing a dBASE 5 (.dbf) file as a data source in a form

Hi..

Tamas Szecsy.....

I've a live solution for you.... have a look at it... let me know if you have doubts regarding this procedure... To access dBASE file into Oracle. You need to install INTERSOLV dbase drivers and configure it for ur connect string to oracle.

Mashkoor.

---

sample1.dbf -- Sample DBF file 
ivdbf15.dll -- dBase ODBC driver 
ivdbf15.hlp -- dBase ODBC driver help 

exec_sql.pll -- ORACLE Supplied EXEC_SQL.pll Forms library exec_sql.pdf -- Help on ORACLE Supplied EXEC_SQL.pll Forms library

Now Read the instructions below completely.

---

Here's is what you need to implement it.

1.
INTERSOLV dBASE datadirect driver (IVDBF15.DLL). This is the ODBC driver for dBase, Clipper, FoxPro and couple more DBF types.

2.
Client Side Dynamic SQL package (EXEC_SQL.pll) supplied by ORACLE on D2K CD. Normally found on

   C:\ORAWIN95\OCA20\PLSQLLIB directory

3.
ORACLE OCA (Open Client Adapter) installed; which can be installed from D2K CD. This facilitates connections to ODBC datasource. But, I also think this might not be really NECESSARY.

---

What I've used in testing:

ORACLE TABLE: sample1

Run the following handy script to create it on your side

CREATE TABLE sample1
(ITEMID VARCHAR2(10)
,QUANTITY NUMBER(10)
,VALUE NUMBER(9,2)
,COMPLETED VARCHAR2(1)
,PO_DATE DATE)

/

DBF File: sample1.dbf with the similar structure (I've three records already created in that)

ITEMID CHAR(10)
QUANTITY NUMBER(10)
VALUE NUMBER(10.2)
COMPLETED LOGICAL (T/F/Y/N)
PO_DATE DATE 8 Note that in dBase the size (10.2) means 10 complete width, 2 decimal places; So the actual integer part is only 7; in other words equivalent of "9999999.99" same as NUMBER(9,2) in ORACLE.

---

Restrictions:

EXEC_SQL can only be used with Developer/2000 products such as Forms 4+, Reports, Graphics and CAN NOT be used from SQL*Plus or PRO*C

---

The procedure name is:
PRC_dBaseToOracle(dBaseConnString IN VARCHAR2)

As such the same procedure can be used to open any type of (DBASE, 2nd ORACLE, ACCESS, EXCEL etc.) secondary connections from any Form.

For your convenience modify that procedure and convert to a LIBRARY that you can attach to other forms and also distribute to the end-users.

---

You would be successful in using the above stuff, only if you have all the requirements I mentioned before.

The procedure:

• IMPORTANT
• I.
• dBASE's LOGICAL datatype is interpreted as RAW(1) by ORACLE and RAW will give you problems.
• Use VARCHAR2 or NUMBER datatype with your ORACLE TABLE to receive such value.
• Check below how I've used column (COMPLETED) which is LOGICAL on dBASE. ---
• II.
• NOTE that dBASE dates are BULLSH*T and not Y2K Compliant!!
• USE 'RRMMDD' to have YEAR 00 to 49 treated as 2000 - 2049
• and YEAR 50 to 99 treated as 1950 - 1999
• Check the example (PO_DATE) below. ---
• III.
• When you use EXEC_SQL.DEFINE_COLUMN with VARCHAR2/CHAR column, you must mention the maximum size
• you expect from dBASE. This procedure will not compile otherwise!
• Check ITEMID, COMPLETED vars below ---
• IV. ---

PROCEDURE PRC_dBaseToOracle(dBaseConnString IN VARCHAR) IS
Sample1Row SAMPLE1%ROWTYPE; -- This statement declares a record of ROW type of TABLE

dBaseConnId EXEC_SQL.ConnType;
dBaseCursor EXEC_SQL.CursType;
ignore PLS_INTEGER;

BEGIN MESSAGE('1111', ACKNOWLEDGE); IF dBaseConnString IS NULL THEN
MESSAGE('2222', ACKNOWLEDGE);

-- First test your dBASE connection thru ODBC on SQLPLUS... 
-- SQL> connect none/none_at_ODBC:dBASEFile 
-- none/none because dBASE doesn't have the concept of USERNAME/PASSWORD 
-- dBASEFile is the DSN (Data Source Name) that you gave when configuring

the ODBC

dBaseConnId := EXEC_SQL.OPEN_CONNECTION('@ODBC:dBASEFile'); ELSE
MESSAGE('3333', ACKNOWLEDGE);
dBaseConnId := EXEC_SQL.OPEN_CONNECTION(dBaseConnString); END IF; MESSAGE('4444');
dBaseCursor := EXEC_SQL.OPEN_CURSOR(dBaseConnId);

• Following statement is to read from SAMPLE1.DBF file

EXEC_SQL.PARSE(dBaseConnId, dBaseCursor
,'SELECT ITEMID, QUANTITY, VALUE, COMPLETED, PO_DATE FROM sample1');

MESSAGE('5555');

EXEC_SQL.DEFINE_COLUMN(dBaseConnId, dBaseCursor, 1, Sample1Row.ITEMID, 10); 
EXEC_SQL.DEFINE_COLUMN(dBaseConnId, dBaseCursor, 2, Sample1Row.QUANTITY); 
EXEC_SQL.DEFINE_COLUMN(dBaseConnId, dBaseCursor, 3, Sample1Row.VALUE); 
EXEC_SQL.DEFINE_COLUMN(dBaseConnId, dBaseCursor, 4, Sample1Row.COMPLETED,

1);
EXEC_SQL.DEFINE_COLUMN(dBaseConnId, dBaseCursor, 5, Sample1Row.PO_DATE); MESSAGE('6666'); ignore := EXEC_SQL.EXECUTE(dBaseConnId, dBaseCursor);

LOOP
MESSAGE('7777');
IF EXEC_SQL.FETCH_ROWS(dBaseConnId, dBaseCursor) > 0 THEN -- This is where the procedure was failing when I tested on PERSONAL ORACLE7

MESSAGE('88881111');
EXEC_SQL.COLUMN_VALUE(dBaseConnId, dBaseCursor, 1, Sample1Row.ITEMID); MESSAGE('88882222');
EXEC_SQL.COLUMN_VALUE(dBaseConnId, dBaseCursor, 2, Sample1Row.QUANTITY); MESSAGE('88883333');
EXEC_SQL.COLUMN_VALUE(dBaseConnId, dBaseCursor, 3, Sample1Row.VALUE); MESSAGE('88883333');
EXEC_SQL.COLUMN_VALUE(dBaseConnId, dBaseCursor, 4, Sample1Row.COMPLETED); MESSAGE('88884444');
EXEC_SQL.COLUMN_VALUE(dBaseConnId, dBaseCursor, 5, Sample1Row.PO_DATE); /*
-- Replace this part with your actual TABLE & COLUMNS... INSERT INTO sample1(ITEMID, QUANTITY, VALUE, COMPLETED, PO_DATE) VALUES (Sample1Row.ITEMID
,Sample1Row.QUANTITY
,Sample1Row.VALUE
,Sample1Row.COMPLETED
,TO_DATE(TO_CHAR(Sample1Row.PO_DATE, 'YYMMDD'), 'RRMMDD'));
COMMIT;
*/

MESSAGE('9999'); MESSAGE
('Sample1Row.ITEMID = '

||Sample1Row.ITEMID 
||' Sample1Row.QUANTITY = ' 
||TO_CHAR(Sample1Row.QUANTITY) 
||' Sample1Row.VALUE = ' 
||TO_CHAR(Sample1Row.VALUE) 
||' Sample1Row.COMPLETED = ' 
||Sample1Row.COMPLETED 
||'Sample1Row.PO_DATE = ' 
||TO_CHAR(TO_DATE(TO_CHAR(Sample1Row.PO_DATE, 'YYMMDD'), 'RRMMDD'),

'YYYYMMDD')
, ACKNOWLEDGE);

ELSE
MESSAGE('0000');
EXIT;
END IF;
END LOOP; MESSAGE('aaaa');

EXEC_SQL.CLOSE_CURSOR(dBaseConnId, dBaseCursor); EXEC_SQL.CLOSE_CONNECTION(dBaseConnId);

EXCEPTION
WHEN EXEC_SQL.PACKAGE_ERROR THEN
-- Remove the following MESSAGE, when DEBUG is over MESSAGE('ERROR (dBaseSource: '
||TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(dBaseConnId))||'): ' ||EXEC_SQL.LAST_ERROR_MESG(dBaseConnId)); MESSAGE('bbbb');

-- This is the general error raised by the EXEC_SQL 
-- package, and denotes an unexpected error in one 
-- of the calls. We can get information on what the 
-- error was as follows: 
-- This prints the error number and error message to 
-- standard out if an error occurred on the destination 
-- connection. 

IF EXEC_SQL.LAST_ERROR_CODE(dBaseConnId) != 0 THEN MESSAGE('cccc');
TEXT_IO.PUT_LINE('ERROR (dBaseSource: ' ||TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(dBaseConnId))||'): ' ||EXEC_SQL.LAST_ERROR_MESG(dBaseConnId));

END IF;
-- We also make sure we free all connections and cursors -- correctly.
IF EXEC_SQL.IS_CONNECTED(dBaseConnId) THEN MESSAGE('dddd');
TEXT_IO.PUT_LINE('ERROR (dBaseSource: ' ||TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(dBaseConnId))||'): ' ||EXEC_SQL.LAST_ERROR_MESG(dBaseConnId));

IF EXEC_SQL.IS_OPEN(dBaseConnId, dBaseCursor) THEN MESSAGE('eeee');
EXEC_SQL.CLOSE_CURSOR(dBaseConnId, dBaseCursor); END IF; EXEC_SQL.CLOSE_CONNECTION(dBaseConnId); MESSAGE('ffff');
END IF;
END;

> -----Original Message-----
> From:	Szecsy Tamas [SMTP:tszecsy_at_GEOMETRIA.hu]
> Sent:	Monday, August 28, 2000 9:50 AM
> To:	Multiple recipients of list ODTUG-DEV2K-L
> Subject:	RE: Accessing a dBASE 5 (.dbf) file as a data source in a
> form
> 
> Try ODBC. I could read/write Excel through ODBC, perhaps it works for
> dBase
> too.
> 
> Tamas Szecsy
> Geometria GIS Systems Hosue Ltd.
> 
> -----Original Message-----
> Sent: Monday, August 28, 2000 7:30 AM
> To: Multiple recipients of list ODTUG-DEV2K-L
> 
> 
> Don't you think it would be easier to manipulate data in dBASE and then
> import into Oracle database?
> Aleem
> 
>  -----Original Message-----
> Sent:	25 August 2000 22:56
> To:	Multiple recipients of list ODTUG-DEV2K-L
> Subject:	Accessing a dBASE 5 (.dbf) file as a data source in a form
> 
> I am looking for information and reference material regarding how to
> access,
> within an Oracle Forms application, data stored in a dBASE 5 .dbf file.  I
> want to be able to import records from the .dbf file and manipulate them
> within the form before updating or creating a related record in an Oracle
> table.  Thank you.

> -- Received on Mon Dec 11 2000 - 12:30:05 CST